# -*- coding: utf-8 -*-

import re
import hashlib
import MySQLdb
from datetime import datetime
import jieba.analyse

from .items import DataItem, ContentItem


# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html


class MySQLPipeline(object):
    db = None
    cursor = None

    def process_item(self, item, spider):
        if spider.name == 'guet':
            self.process_guet(item)
        else:
            pass

    def process_guet(self, item):
        if item.__class__ == DataItem:
            pattern = re.compile(ur'(\d{1,2})\u6708(\d{1,2})\u65e5')  # x月x日
            pattern1 = re.compile(ur'\u53cc\u9009\u4f1a')  # 双选会

            matched = pattern.search(item['name'])
            matched1 = pattern1.search(item['name'])
            if matched:
                date = "%02d%02d" % (int(matched.groups()[0]), int(matched.groups()[1]))

                item['open_date'] = matched.group()
                item['open_date_int'] = int(date)
            elif matched1:
                item['open_date'] = u"双选会"
                item['open_date_int'] = 0000
            else:
                item['open_date'] = u"未知"
                item['open_date_int'] = 1231

            # print item
            self.insert(item)
        else:
            # print item
            mdObj = hashlib.md5()
            mdObj.update(item['content'].encode('utf-8'))
            item['md5'] = mdObj.hexdigest()
            tags = []
            for x, w in jieba.analyse.extract_tags(item['content'], withWeight=True):
                tags.append(x)
                # print tags
            item['tags'] = ",".join(tags)

            self.insert_content(item)

    def open_spider(self, spider):
        self.db = MySQLdb.connect('localhost', 'jobh', 'jobhadmin', 'jobHunter')
        self.cursor = self.db.cursor()

    def close_spider(self, spider):
        self.db.close()

    def insert(self, item):
        sql = 'SELECT id FROM job WHERE url=%s'
        self.cursor.execute(sql, [item['url']])

        result = self.cursor.fetchall()
        if not result:
            sql = '''INSERT INTO job (title, url, source_name, open_date, open_date_int, published_date, create_date, status)
                              VALUES (%s, %s, %s, %s, %s, %s, %s, 1);
                '''

            dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            self.cursor.execute(sql, [item['name'], item['url'], item['source'], item['open_date'], item['open_date_int'], \
                                      item['publish_date'], dt])
            self.db.commit()

    def insert_content(self, item):
        sql = 'SELECT id FROM job WHERE md5 = %s'
        self.cursor.execute(sql, [item['md5']])
        result = self.cursor.fetchall()
        if not result:
            sql = 'UPDATE job SET content = %s, tags = %s, md5 = %s WHERE url = %s'

            self.cursor.execute(sql, [item['content'].encode('utf-8'), item['tags'], item['md5'], item['url']])
            self.db.commit()
